package org.emet.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.time.DateUtils;
import org.emet.entity.biz.CremationHist;
import org.emet.entity.biz.Cremator;
import org.emet.jee.core.dao.Page;
import org.emet.jee.core.dao.arch.ArchDataSourceBaseDao;
import org.emet.service.dto.CremationHistCriteria;
import org.emet.service.dto.CrematorStatus;
import org.emet.service.dto.DatePair;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.SimpleExpression;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

@Repository
public class CremationHistDao extends ArchDataSourceBaseDao<CremationHist, Long> {
	private static final String entityName = CremationHist.class.getSimpleName();
	private static final String crematorEntityName = Cremator.class.getSimpleName();
	@Autowired
	private CrematorDao crematorDao;

	public Page<CremationHist> search(Page<CremationHist> page, CremationHistCriteria criteria) {
		Criteria c = getSession().createCriteria(CremationHist.class);
		NonEmptyValueHashMap<String, Object> propertyNameValues = new NonEmptyValueHashMap<String, Object>();
		propertyNameValues.put("crematorName", criteria.getCrematorName());
		//		propertyNameValues.put("preStartDate", criteria.getPreStartDate());
		Date preStartDate = criteria.getPreStartDate();
		if (preStartDate != null) {
			Date bigPreDate = DateUtils.setHours(preStartDate, 23);
			bigPreDate = DateUtils.setMinutes(bigPreDate, 59);
			c.add(Restrictions.between("preStartDate", preStartDate, bigPreDate));
		}
		//		propertyNameValues.put("startDate", criteria.getStartDate());
		if (criteria.getStartDate() != null) {
			c.add(Restrictions.ge("startDate", criteria.getStartDate()));
		}

		if (criteria.getEndDate() != null) {
			c.add(Restrictions.le("endDate", criteria.getEndDate()));
		}
		propertyNameValues.put("worker", criteria.getWorker());
		propertyNameValues.put("remark", criteria.getRemark());
		c.add(Restrictions.allEq(propertyNameValues));
		page.setOrderBy("id");
		page.setOrder("desc");
		return findPage(page, c);
	}

	public List<CremationHist> search(Date start, Date end) {
		SimpleExpression gt = Restrictions.gt("startDate", start);
		SimpleExpression lt = Restrictions.lt("endDate", end);
		return find(gt, lt);
	}

	public List<CrematorStatus> getCurrCrematorStatus() {
		String hql = "select registration.deadName,startDate from " + entityName
				+ " where crematorId =? and startDate is not null and endDate is null ";
		List<Cremator> all = crematorDao.getAll();
		List<CrematorStatus> result = new ArrayList<CrematorStatus>();
		for (Cremator cremator : all) {
			CrematorStatus cs = new CrematorStatus();
			cs.setName(cremator.getName());
			cs.setStatus(cremator.getStatus());
			List<Object[]> deadInfo = find(hql, cremator.getId());
			int size = deadInfo.size();
			Assert.isTrue(size == 0 || size == 1, "Application Error: deadInfo size is " + size);
			if (size == 1) {
				Object[] dead = deadInfo.get(0);
				cs.setDeadName(dead[0] + "");

				Date startDate = (Date) dead[1];
				cs.setStartTime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(startDate));
			}
			result.add(cs);
		}
		return result;
	}

	public List<DatePair> getIdleTimeRange(Long id, Long crematorId) {
		String hql = " select new org.emet.service.dto.DatePair(startDate,endDate) from " + entityName + " where ";
		if (id != null) {
			hql += " id<>? and";
		}
		hql += "crematorId = ? and startDate > now() and endDate is null order by startDate asc";
		List<DatePair> busyDatePairs;
		if (id != null) {
			busyDatePairs = find(hql, id, crematorId);
		} else {
			busyDatePairs = find(hql, crematorId);
		}
		List<DatePair> idleDatePairs = new ArrayList<DatePair>(busyDatePairs.size() + 1);
		Date beginDate = new Date();
		for (DatePair idp : busyDatePairs) {
			Date endDate = idp.getStartDatetime();
			if (isBefore(beginDate, endDate)) {
				idleDatePairs.add(new DatePair(beginDate, endDate));
			}
			beginDate = idp.getEndDatetime();
		}
		Date endDate = setTime(new Date(), 23, 59, 59);
		if (isBefore(beginDate, endDate)) {
			idleDatePairs.add(new DatePair(beginDate, endDate));
		}

		return idleDatePairs;
	}

	private boolean isBefore(Date d1, Date d2) {
		return d1.before(d2);
	}

	private Date setTime(Date beginDate, int hour, int min, int sec) {
		Calendar instance = Calendar.getInstance();
		instance.setTime(beginDate);
		instance.set(Calendar.HOUR_OF_DAY, hour);
		instance.set(Calendar.MINUTE, min);
		instance.set(Calendar.SECOND, sec);
		return instance.getTime();
	}

	public List<Date> getPreorderedTime(Long crematorId, Date date) {
		String hql = "select preStartDate from " + entityName
				+ " where date(preStartDate)=date(?) and crematorId=? order by preStartDate";
		return find(hql, date, crematorId);
	}

	public List<CremationHist> getAllBusy(Long crematorId) {
		String hql = "from " + entityName + " where crematorId=? and startDate is not null and endDate is null ";
		return find(hql, crematorId);
	}

	public List<CremationHist> findApprove() {
		String hql = "from " + entityName + " where time(preStartDate) <= time(?) and startDate is null";
		Date now = DateUtils.setSeconds(new Date(), 0);
		Date toDate = DateUtils.setSeconds(now, 0);
		toDate = DateUtils.addMinutes(toDate, 5);
		return find(hql, toDate);
	}

	public static void main(String[] args) {
		Date now = DateUtils.setSeconds(new Date(), 0);
		Date toDate = DateUtils.setSeconds(now, 0);
		toDate = DateUtils.addMinutes(toDate, 5);
		System.out.println(now);
		System.out.println(toDate);
	}

	public List<Map<String, Object>> searchByYear(int startYear, int endYear, boolean groupByName) {
		if (!groupByName)
			return getJdbcTemplate()
					.query("SELECT CREMATOR_NAME,YEAR(START_DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM T_CREMATION_HIST WHERE YEAR(START_DATE)>=? AND YEAR(START_DATE) <= ? GROUP BY YEAR(START_DATE)",
							reportRowMap, startYear, endYear);
		else
			return getJdbcTemplate()
					.query("SELECT CREMATOR_NAME,YEAR(START_DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM   T_CREMATION_HIST WHERE YEAR(START_DATE)>=? AND YEAR(START_DATE) <= ?  GROUP BY CREMATOR_NAME,YEAR(START_DATE) ORDER BY CREMATOR_NAME DESC",
							reportRowMap, startYear, endYear);
	}

	public List<Map<String, Object>> searchByMon(int startYear, int startMon, int endYear, int endMon,
			boolean groupByName) {
		if (!groupByName)
			return getJdbcTemplate()
					.query("SELECT CREMATOR_NAME,CONCAT(YEAR(START_DATE),'-',MONTH(START_DATE)) AS DATESTRING,COUNT(ID) AS COUNT FROM T_CREMATION_HIST WHERE (YEAR(START_DATE)>=?  AND MONTH(START_DATE) >= ?) AND (YEAR(START_DATE) <= ? AND MONTH(START_DATE) <= ?) GROUP BY YEAR(START_DATE), MONTH(START_DATE)",
							reportRowMap, startYear, startMon, endYear, endMon);
		else
			return getJdbcTemplate()
					.query("  SELECT CREMATOR_NAME,CONCAT(YEAR(START_DATE),'-',MONTH(START_DATE)) AS DATESTRING,COUNT(ID) AS COUNT FROM T_CREMATION_HIST WHERE (YEAR(START_DATE)>=?  AND MONTH(START_DATE) >= ?) AND (YEAR(START_DATE) <= ? AND MONTH(START_DATE) <= ?) GROUP BY CREMATOR_NAME,YEAR(START_DATE),MONTH(START_DATE) ORDER BY CREMATOR_NAME DESC",
							reportRowMap, startYear, startMon, endYear, endMon);
	}

	public List<Map<String, Object>> searchByDate(Date startDate, Date endDate, boolean groupByName) {
		if (!groupByName)
			return getJdbcTemplate()
					.query(" SELECT CREMATOR_NAME,DATE(START_DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM T_CREMATION_HIST  WHERE DATE(START_DATE)>=DATE(?) AND DATE(START_DATE) <= DATE(?) GROUP BY DATE(START_DATE)",
							reportRowMap, startDate, endDate);
		else
			return getJdbcTemplate()
					.query(" SELECT CREMATOR_NAME,DATE(START_DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM T_CREMATION_HIST WHERE DATE(START_DATE)>=DATE(?) AND DATE(START_DATE) <= DATE(?)  GROUP BY CREMATOR_NAME,DATE(START_DATE) ORDER BY CREMATOR_NAME",
							reportRowMap, startDate, endDate);
	}

	ParameterizedRowMapper<Map<String, Object>> reportRowMap = new ParameterizedRowMapper<Map<String, Object>>() {
		public Map<String, Object> mapRow(ResultSet rs, int paramInt) throws SQLException {
			Map<String, Object> c = new HashMap<String, Object>(1);
			c.put("count", rs.getInt("COUNT"));
			c.put("dateString", rs.getString("DATESTRING"));
			c.put("crematorName", rs.getString("CREMATOR_NAME"));
			return c;
		}
	};

	public List<CremationHist> findUnpaiedByBiz(String bizNo) {
		return find("from CremationHist where paied = false and registration.bizNo = ?", bizNo);
	}

	public void setAsPaied(Long chargeId) {
		CremationHist cremationHist = get(chargeId);
		if (null != cremationHist) {
			cremationHist.setPaied(true);
		}

	}
}
